<?php

namespace app\services;

use app\lib\exception\ParamException;
use app\model\CodPerformance;
use app\model\CodStatement;
use app\utils\CharacterUtils;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use think\facade\Cache;

class CodStatementService
{


    /**
     * @var CodPerformance
     */
    private $dao;


    public function __construct()
    {
        $this->dao = new CodStatement();
    }


    private $remarkArr=
        [
            'E' => ['经济舱', 'Economy'],
            'B' => ['商务舱', 'Business'],
            'F' => ['头等舱', 'First'],
            '硬座' => ['硬座', '硬座'],
            '无座' => ['无座', '无座'],
            '一等座' => ['一等座', '一等座'],
            '二等座' => ['二等座', '二等座'],
            '商务座' => ['商务座', '商务座'],
            '特等座' => ['特等座', '特等座'],
            '豪华位' => ['豪华位', '豪华位'],
            '尊豪位' => ['尊豪位', '尊豪位'],
            '标准舱' => ['标准舱', '标准舱'],
            '头等舱' => ['头等舱', '头等舱'],
        ];

    private $remarksArr=
        [
            'A' => ['', 'Air'],
            'T' => ['高铁', 'Train'],
            'H' => ['酒店', 'Hotel'],
            'S' => ['船票', 'Ship'],
        ];




    private function setCodStatement($airlines, $voyage, $orderArr, $fly_time, $passenger, $from, $to, $comp, $price, $quantity_of_tickets, $salesList, $infoArr, $remark = '')
    {



        $booking_date = date('d/m/y', strtotime($orderArr[16]));
        return [
            //分组起飞日期
            'voyage' => $voyage,
            'fly_day' => strtoupper(date('dM', $fly_time)),
            'booking_date' => $booking_date,
            'description' => $passenger,
            'flight_destination_from' => $from,
            'flight_destination_to' => $to,
            'flight_date' => date('d/m/y', $fly_time),
            'flight_time' => $infoArr[0]??'',
            'patron_id' => $infoArr[1]??'',
            'each_comp' => $comp,
            'unit_price' => $price,
            'quantity_of_tickets' => $quantity_of_tickets,
            'amount' => $price,
            'charge_reason' => '',
            'void_comp' => $remark,
            'air_macau_promotion' => $airlines['type'],
            'airline' => $airlines['name'],
            'invoice_no' => $orderArr[18],
            'remark' => $this->remarkArr[$infoArr[3]??''][1]??'',
            'booking' => $salesList['Name']??'',
            'tm' => $salesList['Staff ID']??'',
            'property' => $salesList['Property']??'',
            'date_of_email_sent' => $booking_date,
            'date_of_email_reply' => '',
            'comp' => '',
            'remarks' => $infoArr[4]??'',
        ];
    }


    public function setBatchSaveCodStatementList($orderArr, $cityArr, $airlinesArr)
    {
        //获取备注
        if (preg_match('/T\d{16,19}退/', $orderArr[60], $remark)){
            $remark = $remark[0];
        }else{
            $remark ='';
        }


        //'22:05#6294219#1008969#E#A
        //23：00#会员号#员工号#仓种#类型（air）
        $infoArr = explode('#', preg_replace('/\`/', '', $orderArr[30]));

        if (!preg_match('/\#/',  $orderArr[30])){
            $infoArr = ['','','','',''];
        }

        $airlines = [
            'type' => 'Other Airline',
            'name' => '高铁',
        ];


        if (array_key_exists(4, $infoArr)) {
            if ($infoArr[4] == 'A') {
                $infoArr[4] = 'AIR';
                if (preg_match('/\w{3}/', $orderArr[26], $ticket_header) && !empty($airlinesArr[$ticket_header[0]]) ) {
                    if ($ticket_header[0] == 675) {
                        $airlines['type'] = 'Sales Request';
                    }
                    $airlines['name'] = $airlinesArr[$ticket_header[0]];
                }
            }else{
                $remarksInfo = $this->remarksArr[$infoArr[4]]??['',''];
                $cityArr = [];
                $infoArr[4] = $remarksInfo[1];
                $airlines['name'] = $remarksInfo[0];
            }


        }



//        dd($infoArr);
        $salesListService = new SalesListService();
        $salesList = $salesListService->getSalesFromRedis($infoArr[2]??'');


        $comp = '欠';
        $quantity_of_tickets = 1;
        $result = [];


        $fly_time = strtotime($orderArr[27]);
        $passengerList = array_diff(preg_split('/\/\s+/', $orderArr['53']), [null, '']);;
        $passengerListCountArr = array_count_values($passengerList);
        $price = (int)$orderArr['8'] / count($passengerListCountArr);
        $voyageArr = preg_split('/\-/', $orderArr[21]);



        //退票
        if ((int)$orderArr[23] < 0 ) {
            $remark = $orderArr[18].'退票';
            $comp = 'NO NEED';
            $quantity_of_tickets = -1;
            //退票单一个人一个行程的时候
            if (count($passengerList) == 1 ) {

                if (count($voyageArr) == 2){
                    $result['good'][] = $this->setCodStatement($airlines,
                        $voyageArr[0] . $voyageArr[1],
                        $orderArr,
                        $fly_time,
                        $passengerList[0],
                        $cityArr[$voyageArr[0]] ?? $voyageArr[0],
                        $cityArr[$voyageArr[1]] ?? $voyageArr[1],
                        $comp,
                        $price,
                        $quantity_of_tickets,
                        $salesList,
                        $infoArr,
                        $remark
                    );
                    return $result;
                }
                if (count($voyageArr) == 3 && $voyageArr[0] == $voyageArr[2]){
                    $result['good'][] = $this->setCodStatement($airlines,
                        $voyageArr[0] . $voyageArr[1],
                        $orderArr,
                        $fly_time,
                        $passengerList[0],
                        $cityArr[$voyageArr[0]] ?? $voyageArr[0],
                        $cityArr[$voyageArr[1]] ?? $voyageArr[1],
                        $comp,
                        $price,
                        $quantity_of_tickets,
                        $salesList,
                        $infoArr,
                        $remark
                    );
                    $result['good'][] = $this->setCodStatement($airlines,
                        $voyageArr[0] . $voyageArr[1],
                        $orderArr,
                        $fly_time,
                        $passengerList[0],
                        $cityArr[$voyageArr[1]] ?? $voyageArr[1],
                        $cityArr[$voyageArr[2]] ?? $voyageArr[2],
                        $comp,
                        0,
                        $quantity_of_tickets,
                        $salesList,
                        $infoArr,
                        $remark
                    );
                    return $result;
                }
            }


            $_voyageArr = [];
            foreach ($voyageArr as $voyage){
                $_voyageArr[] = $cityArr[$voyage] ?? $voyage;
            }


            $result['reform'][] = $this->setCodStatement($airlines,
                $voyageArr[0] . $voyageArr[1],
                $orderArr,
                $fly_time,
                $passengerList,
                $voyageArr,
                $_voyageArr,
                $comp,
                (int)$orderArr['8'],
                '-1',
                $salesList,
                $infoArr,
                $remark
            );
            return $result;
        }




        if (preg_match('/改.*/', $orderArr[21], $a)){
            $voyageArr[count($voyageArr)-1] = preg_replace('/改.+/', '', $voyageArr[count($voyageArr)-1]);

            $remark = $this->getInvoiceNoBy($passengerList[0], $voyageArr[0].$voyageArr[1], strtoupper(date('dM', $fly_time)) )['invoice_no']?? '改期费';
        }

        if (count($voyageArr) == 2) {
            foreach ($passengerList as $passenger) {
                $result['good'][] = $this->setCodStatement($airlines,
                    $voyageArr[0] . $voyageArr[1],
                    $orderArr,
                    $fly_time,
                    $passenger,
                    $cityArr[$voyageArr[0]] ?? $voyageArr[0],
                    $cityArr[$voyageArr[1]] ?? $voyageArr[1],
                    $comp,
                    $price,
                    $quantity_of_tickets,
                    $salesList,
                    $infoArr,
                    $remark
                );
            }

        } elseif (count($voyageArr) == 3) {

            //往返
            if ($voyageArr[0] == $voyageArr[2]) {

                foreach ($passengerList as $passenger) {
                    //第一层
                    $result['good'][] = $this->setCodStatement($airlines,
                        $voyageArr[0] . $voyageArr[1],
                        $orderArr,
                        $fly_time,
                        $passenger,
                        $cityArr[$voyageArr[0]] ?? $voyageArr[0],
                        $cityArr[$voyageArr[1]] ?? $voyageArr[1],
                        $comp,
                        $price,
                        $quantity_of_tickets,
                        $salesList,
                        $infoArr,
                        $remark
                    );
                    //第二层
                    $result['good'][] = $this->setCodStatement($airlines,
                        $voyageArr[1] . $voyageArr[2],
                        $orderArr,
                        $fly_time,
                        $passenger,
                        $cityArr[$voyageArr[1]] ?? $voyageArr[1],
                        $cityArr[$voyageArr[2]] ?? $voyageArr[2],
                        $comp,
                        0,
                        0,
                        $salesList,
                        $infoArr,
                        $remark
                    );
                }


            } else {
                //联程

                foreach ($passengerListCountArr as $name => $passengerCount) {

                    $result['connecting'][] = [
                        //第一层
                        $this->setCodStatement($airlines,
                            $voyageArr[0] . $voyageArr[1],
                            $orderArr,
                            $fly_time,
                            $name,
                            $cityArr[$voyageArr[0]] ?? $voyageArr[0],
                            $cityArr[$voyageArr[1]] ?? $voyageArr[1],
                            $comp,
                            $price,
                            $quantity_of_tickets,
                            $salesList,
                            $infoArr,
                            $remark
                        ),
                        //第二层
                        $this->setCodStatement($airlines,
                            $voyageArr[1] . $voyageArr[2],
                            $orderArr,
                            $fly_time,
                            $name,
                            $cityArr[$voyageArr[1]] ?? $voyageArr[1],
                            $cityArr[$voyageArr[2]] ?? $voyageArr[2],
                            $comp,
                            0,
                            $quantity_of_tickets,
                            $salesList,
                            $infoArr,
                            $remark
                        )


                    ];
                }


            }


        } else {
            $result['error'][] = $orderArr;
            return $result;
        }


        return $result;
    }


    public function getInvoiceNoBy($name, $voyage, $flyDay)
    {
        return $this->dao->where('description', '=', $name)->where('voyage', '=', $voyage)->where('fly_day', '=', $flyDay)->find();
    }

    public function saveGood($data){
        array_map(function ($statement){
            if ($statement['quantity_of_tickets'] = -1)$this->dao->where('invoice_no', $statement['invoice_no'])->update(['each_comp' => 'NO NEED']);
        }, $data);
        return $this->dao->saveAll($data);
    }

    public function saveConnecting($data){
        return array_map(function ($statementArr) {

            return$this->dao->saveAll($statementArr);
        }, $data);
    }

    public function saveReform($data)
    {

        $saveArr = [];
        foreach ($data as $datum){
            $voyageArr = explode('-',$datum['flight_destination_to']);

            if (count($voyageArr)>2) {
                //第一程
                $datum['flight_destination_from'] = $voyageArr[0];
                $datum['flight_destination_to'] = $voyageArr[1];
                $saveArr[] = $datum;
                //第2程
                $datum['flight_destination_from'] = $voyageArr[1];
                $datum['flight_destination_to'] = $voyageArr[0];
                $datum['unit_price'] = '';
                $datum['quantity_of_tickets'] = '';
                $datum['amount'] = '';
                $saveArr[] = $datum;
            }else{
                $datum['flight_destination_from'] = $voyageArr[0];
                $datum['flight_destination_to'] = $voyageArr[1];
                $saveArr[] = $datum;
            }
        }
        return $this->dao->saveAll($saveArr);
    }

    public function saveGood2redis($key, $data){
//        array_map(function ($statement){
//            if ($statement['quantity_of_tickets'] = -1)$this->dao->where('invoice_no', $statement['invoice_no'])->update(['each_comp' => 'NO NEED']);
//        }, $data);

        return Cache::store('redis')->hSet($key, 'good', json_encode($data));
    }

    public function saveConnecting2redis($key, $data){
        $result = [];
        foreach ($data as $datum){
            foreach ($datum as $item) {
                $result[] = $item;
            }
        }

        return Cache::store('redis')->hSet($key, 'connecting', json_encode($result));
    }

    public function saveReform2redis($key, $data)
    {

        $saveArr = [];
        foreach ($data as $datum){
            $voyageArr = explode('-',$datum['flight_destination_to']);

            if (count($voyageArr)>2) {
                //第一程
                $datum['flight_destination_from'] = $voyageArr[0];
                $datum['flight_destination_to'] = $voyageArr[1];
                $saveArr[] = $datum;
                //第2程
                $datum['flight_destination_from'] = $voyageArr[1];
                $datum['flight_destination_to'] = $voyageArr[0];
                $datum['unit_price'] = '';
                $datum['quantity_of_tickets'] = '';
                $datum['amount'] = '';
                $saveArr[] = $datum;
            }else{
                $datum['flight_destination_from'] = $voyageArr[0];
                $datum['flight_destination_to'] = $voyageArr[1];
                $saveArr[] = $datum;
            }
        }
        return Cache::store('redis')->hSet($key, 'reform', json_encode($saveArr));
    }


    public function toExcel(array $dayArr){
        $characterUtils = new CharacterUtils();


        $rand = 'ENERGY&BUSINESS'.date('dM~',  $dayArr[0]).date('dM',  $dayArr[count($dayArr)-1]);
        $spreadsheet = new Spreadsheet();
        foreach ($dayArr as $key => $day){

            $fly_day = date('dM', $day);
            $rand .= ','.$fly_day;
            $worksheet = $spreadsheet->createSheet($key)->setTitle($fly_day);
            $worksheet = $this->setTableHeader($worksheet, $day);

            $codStatements = $this->dao->where('fly_day', '=', $fly_day)->order('flight_time', 'asc')->select();
            $r = 5;
            foreach ($codStatements as $codStatement){

                $r ++;
                $worksheet->setCellValue('A'.$r, $characterUtils->c2t( $codStatement['booking_date']));
                $worksheet->setCellValue('B'.$r, $characterUtils->c2t( $codStatement['description']));
                $worksheet->setCellValue('C'.$r, $characterUtils->c2t( $codStatement['flight_destination_from']));
                $worksheet->setCellValue('D'.$r, $characterUtils->c2t( $codStatement['flight_destination_to']));
                $worksheet->setCellValue('E'.$r, $characterUtils->c2t( $codStatement['flight_date']));
                $worksheet->setCellValue('F'.$r, $characterUtils->c2t( $codStatement['flight_time']));
                $worksheet->setCellValue('G'.$r, $characterUtils->c2t( $codStatement['patron_id']));
                $worksheet->setCellValue('H'.$r, $characterUtils->c2t( $codStatement['each_comp']));
                $worksheet->setCellValue('I'.$r, $characterUtils->c2t( $codStatement['unit_price']));
                $worksheet->setCellValue('J'.$r, $characterUtils->c2t( $codStatement['quantity_of_tickets']));
                $worksheet->setCellValue('K'.$r, $characterUtils->c2t( $codStatement['amount']));
                $worksheet->setCellValue('L'.$r, $characterUtils->c2t( $codStatement['charge_reason']));
                $worksheet->setCellValue('M'.$r, $characterUtils->c2t( $codStatement['void_comp']));
                $worksheet->setCellValue('N'.$r, $characterUtils->c2t( $codStatement['air_macau_promotion']));
                $worksheet->setCellValue('O'.$r, $characterUtils->c2t( $codStatement['airline']));
                $worksheet->setCellValue('P'.$r, $characterUtils->c2t( $codStatement['invoice_no']));
                $worksheet->setCellValue('Q'.$r, $characterUtils->c2t( $codStatement['remark']));
                $worksheet->setCellValue('R'.$r, $characterUtils->c2t( $codStatement['booking']));
                $worksheet->setCellValue('S'.$r, $characterUtils->c2t( $codStatement['tm']));
                $worksheet->setCellValue('T'.$r, $characterUtils->c2t( $codStatement['property']));
                $worksheet->setCellValue('U'.$r, $characterUtils->c2t( $codStatement['date_of_email_sent']));
                $worksheet->setCellValue('V'.$r, $characterUtils->c2t( $codStatement['date_of_email_reply']));
                $worksheet->setCellValue('W'.$r, $characterUtils->c2t( $codStatement['comp']));
                $worksheet->setCellValue('X'.$r, $characterUtils->c2t( $codStatement['remarks']));


                if ($codStatement['each_comp'] == 'NO NEED'){
                    $worksheet->getStyle('H'.$r)->getFont()->getColor()->setARGB('FF0000');
                }
                if (empty($codStatement['quantity_of_tickets'])){
                    $worksheet->getStyle('I'.$r.':K'.$r)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('C9E4B4');
                }
                if ($codStatement['quantity_of_tickets']<0){
                    $worksheet->getStyle('H'.$r.':K'.$r)->getFont()->getColor()->setARGB('FF0000');
                }



                $worksheet->getRowDimension($r)->setRowHeight(45);
            }

            $worksheet->getStyle('A5:X'.$r)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);


            $worksheet->getStyle('A5:X'.$r)->applyFromArray([
                'borders' => [
                    'allBorders' => [
                        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                        'color' => ['argb' => '00000000'],
                    ],
                ],
            ]);
            $worksheet->getRowDimension('5')->setRowHeight(60);
        }


        $writer = new Xlsx($spreadsheet);
        $filename = 'storage/excel/'.$rand.'.xlsx';

        $writer->save(app()->getRootPath() . 'public/'. $filename);

        return env('app.netaddress').$filename;
    }
    public function redis2Excel($key){
        $characterUtils = new CharacterUtils();

        $all = Cache::store('redis')->hGetAll($key);
        if (empty($all)) return null;

        $data = [];
        empty($all['good']) || $data = array_merge($data,  json_decode($all['good'], true));
        empty($all['connecting']) || $data = array_merge($data,  json_decode($all['connecting'], true));
        empty($all['reform']) || $data = array_merge($data,  json_decode($all['reform'], true));


        $rand = 'ENERGY&BUSINESS-'.$key;
        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->createSheet();
        $worksheet = $this->setTableHeader($worksheet, '0');
        $r = 5;
        foreach ($data as $codStatement){

            $r ++;
            $worksheet->setCellValue('A'.$r, $characterUtils->c2t( $codStatement['booking_date']));
            $worksheet->setCellValue('B'.$r, $characterUtils->c2t( $codStatement['description']));
            $worksheet->setCellValue('C'.$r, $characterUtils->c2t( $codStatement['flight_destination_from']));
            $worksheet->setCellValue('D'.$r, $characterUtils->c2t( $codStatement['flight_destination_to']));
            $worksheet->setCellValue('E'.$r, $characterUtils->c2t( $codStatement['flight_date']));
            $worksheet->setCellValue('F'.$r, $characterUtils->c2t( $codStatement['flight_time']));
            $worksheet->setCellValue('G'.$r, $characterUtils->c2t( $codStatement['patron_id']));
            $worksheet->setCellValue('H'.$r, $characterUtils->c2t( $codStatement['each_comp']));
            $worksheet->setCellValue('I'.$r, $characterUtils->c2t( $codStatement['unit_price']));
            $worksheet->setCellValue('J'.$r, $characterUtils->c2t( $codStatement['quantity_of_tickets']));
            $worksheet->setCellValue('K'.$r, $characterUtils->c2t( $codStatement['amount']));
            $worksheet->setCellValue('L'.$r, $characterUtils->c2t( $codStatement['charge_reason']));
            $worksheet->setCellValue('M'.$r, $characterUtils->c2t( $codStatement['void_comp']));
            $worksheet->setCellValue('N'.$r, $characterUtils->c2t( $codStatement['air_macau_promotion']));
            $worksheet->setCellValue('O'.$r, $characterUtils->c2t( $codStatement['airline']));
            $worksheet->setCellValue('P'.$r, $characterUtils->c2t( $codStatement['invoice_no']));
            $worksheet->setCellValue('Q'.$r, $characterUtils->c2t( $codStatement['remark']));
            $worksheet->setCellValue('R'.$r, $characterUtils->c2t( $codStatement['booking']));
            $worksheet->setCellValue('S'.$r, $characterUtils->c2t( $codStatement['tm']));
            $worksheet->setCellValue('T'.$r, $characterUtils->c2t( $codStatement['property']));
            $worksheet->setCellValue('U'.$r, $characterUtils->c2t( $codStatement['date_of_email_sent']));
            $worksheet->setCellValue('V'.$r, $characterUtils->c2t( $codStatement['date_of_email_reply']));
            $worksheet->setCellValue('W'.$r, $characterUtils->c2t( $codStatement['comp']));
            $worksheet->setCellValue('X'.$r, $characterUtils->c2t( $codStatement['remarks']));


            if ($codStatement['each_comp'] == 'NO NEED'){
                $worksheet->getStyle('H'.$r)->getFont()->getColor()->setARGB('FF0000');
            }
            if (empty($codStatement['quantity_of_tickets'])){
                $worksheet->getStyle('I'.$r.':K'.$r)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('C9E4B4');
            }
            if ($codStatement['quantity_of_tickets']<0){
                $worksheet->getStyle('H'.$r.':K'.$r)->getFont()->getColor()->setARGB('FF0000');
            }



            $worksheet->getRowDimension($r)->setRowHeight(45);
        }

        $worksheet->getStyle('A5:X'.$r)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);


        $worksheet->getStyle('A5:X'.$r)->applyFromArray([
            'borders' => [
                'allBorders' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                    'color' => ['argb' => '00000000'],
                ],
            ],
        ]);
        $worksheet->getRowDimension('5')->setRowHeight(60);


        $writer = new Xlsx($spreadsheet);
        $filename = 'storage/excel/'.$rand.'.xlsx';

        $writer->save(app()->getRootPath() . 'public/'. $filename);

        return env('app.netaddress').$filename;
    }

    private function setTableHeader($worksheet, $day){

        $worksheet->getStyle('A:X')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $worksheet->getColumnDimension('A')->setAutoSize(true);
        $worksheet->getColumnDimension('B')->setAutoSize(true);
        $worksheet->getColumnDimension('C')->setAutoSize(true);
        $worksheet->getColumnDimension('D')->setAutoSize(true);
        $worksheet->getColumnDimension('E')->setAutoSize(true);
        $worksheet->getColumnDimension('F')->setAutoSize(true);
        $worksheet->getColumnDimension('G')->setAutoSize(true);
        $worksheet->getColumnDimension('H')->setAutoSize(true);
        $worksheet->getColumnDimension('I')->setAutoSize(true);
        $worksheet->getColumnDimension('J')->setAutoSize(true);
        $worksheet->getColumnDimension('K')->setAutoSize(true);
        $worksheet->getColumnDimension('L')->setAutoSize(true);
        $worksheet->getColumnDimension('M')->setAutoSize(true);
        $worksheet->getColumnDimension('N')->setAutoSize(true);
        $worksheet->getColumnDimension('O')->setAutoSize(true);
        $worksheet->getColumnDimension('P')->setAutoSize(true);
        $worksheet->getColumnDimension('Q')->setAutoSize(true);
        $worksheet->getColumnDimension('R')->setAutoSize(true);
        $worksheet->getColumnDimension('S')->setAutoSize(true);
        $worksheet->getColumnDimension('T')->setAutoSize(true);
        $worksheet->getColumnDimension('U')->setAutoSize(true);
        $worksheet->getColumnDimension('V')->setAutoSize(true);
        $worksheet->getColumnDimension('W')->setAutoSize(true);
        $worksheet->getColumnDimension('X')->setAutoSize(true);
        $worksheet->setCellValue('A1','Name of Travel Agent (Energy Travel Agency Ltd )')->mergeCells('A1:C1');
        $worksheet->setCellValue('A3','Summary of Purchased for the month of ____'.date('M Y', $day).'_______')->mergeCells('A3:C3');
        //设置表头内容
        $worksheet->setCellValue('A5', "Booking Date \n (DD/MM/YY)");
        $worksheet->setCellValue('B5', "Description\n(incl. guest's name & route)");
        $worksheet->setCellValue('C5', "Flight Destination\nFrom");
        $worksheet->setCellValue('D5', "Flight Destination\nTo");
        $worksheet->setCellValue('E5', "Flight date\n(DD/MM/YY)");
        $worksheet->setCellValue('F5', "Flight time\n(HH:MM)");
        $worksheet->setCellValue('G5', "Patron ID");
        $worksheet->setCellValue('H5', "Comp # (each \nrecord for one\n comp slip)");
        $worksheet->setCellValue('I5', "Unit Price");
        $worksheet->setCellValue('J5', "Quanti\nty of\n tickets");
        $worksheet->setCellValue('K5', "Amount (HKD)");
        $worksheet->setCellValue('L5', "Charge \nReason^");
        $worksheet->setCellValue('M5', "Void Comp#");
        $worksheet->setCellValue('N5', "Air Macau Promotion");
        $worksheet->setCellValue('O5', "Airline");
        $worksheet->setCellValue('P5', "Invoice No.");
        $worksheet->setCellValue('Q5', "Remark");
        $worksheet->setCellValue('R5', "Booking");
        $worksheet->setCellValue('S5', "TM #");
        $worksheet->setCellValue('T5', "Property");
        $worksheet->setCellValue('U5', "Date of email sent");
        $worksheet->setCellValue('V5', "Date of email reply");
        $worksheet->setCellValue('W5', "COMP#");
        $worksheet->setCellValue('X5', "Remarks");

        $worksheet->getStyle('A5:X5')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('F4B382');
        return$worksheet;
    }



}


